package testshop.dao;

import testshop.db.DbClose;
import testshop.db.DbConn;
import testshop.entity.Goods;
import testshop.tools.ScannerChoice;

import java.sql.*;
import java.util.ArrayList;


public  final class GoodsDao {

    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;




      /**
           * 方法实现说明
           * @method      向数据库添加商品
           * @author      CanZhou
           * @version     1.0
           * @return      是否成功插入数据
           * @exception
           * @date        2018/3/16 09:46
           */

   public boolean addGoods(Goods goods){

       boolean bool =  false;



       String sql = " INSERT INTO goods(gname,gprice,gnum) VALUES(?,?,?)";

       try{

           conn = DbConn.getconn();

           System.out.println("正在连接数据库-01");
           pstmt = conn.prepareStatement(sql);
           pstmt.setString(1,goods.getGname());
           pstmt.setDouble(2,goods.getGprice());
           pstmt.setInt(3,goods.getGnum());
           int rs = pstmt.executeUpdate();

           if (rs>0){
               bool = true;
           }
       }catch (SQLException e){
           e.printStackTrace();
       }finally {
           DbClose.addClose(pstmt,conn);
       }

      return  bool;




   }


   public boolean deleteGoods(int gid){

       boolean bool = false;
       conn = DbConn.getconn();

       String sql = "DELETE FROM goods WHERE gid = ?";
       try {
           pstmt = conn.prepareStatement(sql);
           pstmt.setInt(1,gid);
        int  rs = pstmt.executeUpdate();

           if (rs > 0){

               bool = true;

           }





       }catch (SQLException e){
           e.printStackTrace();
       }


       return  bool;
   }


   public ArrayList<Goods> queryGoods(int chioce){

       ArrayList<Goods> list = new ArrayList<Goods>();
       conn = DbConn.getconn();
       switch (chioce){

           case 1:

               String sqlGnum = "SELECT * FROM goods ORDER BY gnum ASC";
               try{

                   pstmt = conn.prepareStatement(sqlGnum);
                   rs = pstmt.executeQuery();
                   while (rs.next()){

                       int gid = rs.getInt("gid");
                       String gname = rs.getString("gname");
                       double gprice = rs.getDouble("gprice");
                       int gnum = rs.getInt("gnum");

                       Goods g = new Goods(gid,gname,gprice,gnum);

                       list.add(g);




                   }


               }catch (SQLException e){
                   e.printStackTrace();
               }finally {
                   DbClose.queryClose(pstmt,rs,conn);
               }




               break;
           case 2:

               String sqlGprice = "SELECT * FROM goods ORDER BY gprice ASC";
               try{

                   pstmt = conn.prepareStatement(sqlGprice);
                   rs = pstmt.executeQuery();
                   while (rs.next()){

                       int gid = rs.getInt("gid");
                       String gname = rs.getString("gname");
                       double gprice = rs.getDouble("gprice");
                       int gnum = rs.getInt("gnum");

                       Goods g = new Goods(gid,gname,gprice,gnum);

                       list.add(g);


                   }


               }catch (SQLException e){
                   e.printStackTrace();
               }finally {
                   DbClose.queryClose(pstmt,rs,conn);
               }




               break;
           case 3:

               String nameGet = ScannerChoice.ScannerInfoString();

               String lastName = "%" + nameGet + "%";


               String sqlGname = "SELECT * FROM goods WHERE gname LIKE ?";
               System.out.println(sqlGname);
               System.out.println(nameGet);

               try{
                   pstmt = conn.prepareStatement(sqlGname);
                   pstmt.setString(1,lastName);
                   rs = pstmt.executeQuery();

                   while (rs.next()){

                     int gid = rs.getInt("gid");
                     String gname = rs.getString(2);
                     double gprice = rs.getDouble(3);
                     int gnum = rs.getInt(4);

                     Goods entiry = new Goods(gid,gname,gprice,gnum);

                     list.add(entiry);

                   }


               }catch (SQLException e){
                   e.printStackTrace();
               }finally {
                   DbClose.queryClose(pstmt,rs,conn);
               }





               break;
           default:
               break;


       }



      return  list;


   }


   public  ArrayList<Goods> showAllGoods(){

       ArrayList<Goods> list = new ArrayList<Goods>();

       conn = DbConn.getconn();

       String sql = "SELECT * FROM goods";

      try {
          pstmt = conn.prepareStatement(sql);
          rs = pstmt.executeQuery();
          while (rs.next()){

              int gid = rs.getInt("gid");
              String name = rs.getString(2);
              double price = rs.getDouble(3);
              int num = rs.getInt(4);


              Goods entiy = new Goods(gid,name,price,num);
              list.add(entiy);


          }



      }catch (SQLException e){
          e.printStackTrace();
      }




       return  list;


   }


   public  boolean updateGoods(int key,Goods goods){
       boolean bool =  false;
       conn = DbConn.getconn();
       switch (key){

           case 1:

               String sql = " UPDATE goods SET gname = ? WHERE gid = ? ";

               try{
                   pstmt = conn.prepareStatement(sql);
                   pstmt.setString(1,goods.getGname());
                   pstmt.setInt(2,goods.getGid());

                   int rs = pstmt.executeUpdate();

                   if(rs > 0){


                       bool = true;

                   }





               }catch (SQLException e){
                   e.printStackTrace();
               }finally {
                   DbClose.addClose(pstmt,conn);
               }




                break;

           case 2:

               String sqlprice = " UPDATE goods SET gprice = ? WHERE gid = ? ";

               try{
                   pstmt = conn.prepareStatement(sqlprice);
                   pstmt.setDouble(1,goods.getGprice());
                   pstmt.setInt(2,goods.getGid());

                   int rs = pstmt.executeUpdate();

                   if(rs > 0){


                       bool = true;

                   }


               }catch (SQLException e){
                   e.printStackTrace();
               }finally {
                   DbClose.addClose(pstmt,conn);
               }




               break;


           case 3:

               String sqlNum = " UPDATE goods SET gnum = ? WHERE gid = ? ";

               try{
                   pstmt = conn.prepareStatement(sqlNum);
                   pstmt.setInt(1,goods.getGnum());
                   pstmt.setInt(2,goods.getGid());

                   int rs = pstmt.executeUpdate();

                   if(rs > 0){


                       bool = true;

                   }





               }catch (SQLException e){
                   e.printStackTrace();
               }finally {
                   DbClose.addClose(pstmt,conn);
               }




               break;


           default:
              break;
       }


       return bool;


   }







}
